﻿using NPOI.HSSF.UserModel;
using NPOI.POIFS.FileSystem;
using NPOI.SS.Converter;
using NPOI.SS.Format;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Coldairarrow.Util.Excel.Util
{
    public class ExcelToHtmlUtil
    {
        public static IWorkbook wbook = null;
        private IWorkbook wb = null;

        private const string DEFAULTS_CLASS = "excelDefaults";

        private int firstColumn;
        private int endColumn;

        private bool gotBounds;

        public ExcelToHtmlUtil()
        {
        }

        public ExcelToHtmlUtil(FileStream file)
        {
            IWorkbook workbook = ExcelUtil.GetWorkbook(file);
            this.wb = workbook;
        }

        public ExcelToHtmlUtil(IWorkbook workbook)
        {
            this.wb = workbook;
        }

        public ExcelToHtmlUtil(string filePath)
        {
            IWorkbook workbook = ExcelUtil.GetWorkbook(filePath);
            this.wb = workbook;
        }

        public string GetHtmlString(string filePath, string sheetName)
        {
            IWorkbook workbook = ExcelUtil.GetWorkbook(filePath);
            this.wb = workbook;
            return this.ToHtml(sheetName, false);
        }


        public string GetHtmlString(FileStream file,string sheetName)
        {
            IWorkbook workbook = ExcelUtil.GetWorkbook(file);
            this.wb = workbook;
            return this.ToHtml(sheetName,false);
        }

        public string[] GetSheetNameArry(FileStream file)
        {
            IWorkbook workbook = ExcelUtil.GetWorkbook(file);
            wbook = workbook;
            return ExcelUtil.GetSheetNames(workbook);
        }

        public string[] GetSheetNameArry(string filePath)
        {
            IWorkbook workbook = ExcelUtil.GetWorkbook(filePath);
            wbook = workbook;
            return ExcelUtil.GetSheetNames(workbook);
        }
        public string[] GetSheetNameArry(IWorkbook workbook)
        {
            return ExcelUtil.GetSheetNames(workbook);
        }
        public string GetHtmlString(IWorkbook workbook, string sheetName)
        {
      
            this.wb = workbook;
           
            return this.ToHtml(sheetName, false);
        }

        /// <summary>
        /// 新增ExcelToHtml方法
        /// </summary>
        /// <param name="sheetName"></param>    
        /// <param name="workbook"></param>
      
        public string ExcelToHtml(string sheetName, IWorkbook workbook)
        {
      
            
            ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter();
            // 设置输出参数
            excelToHtmlConverter.OutputColumnHeaders = true;
            excelToHtmlConverter.OutputHiddenColumns = false;
            excelToHtmlConverter.OutputHiddenRows = false;
            excelToHtmlConverter.OutputLeadingSpacesAsNonBreaking = true;
            excelToHtmlConverter.OutputRowNumbers = true;
            excelToHtmlConverter.UseDivsToSpan = true;
            // 处理的Excel文件
            //excelToHtmlConverter.ProcessSheet(workbook);
            excelToHtmlConverter.ProcessWorkbook(workbook);
            //添加表格样式
            //excelToHtmlConverter.Document.InnerXml =
            //    excelToHtmlConverter.Document.InnerXml.Insert(
            //        excelToHtmlConverter.Document.InnerXml.IndexOf("<head>", 0) + 6,
            //        @"<style>table, td, th{border:1px solid green;}th{background-color:green;color:white;}</style>"
            //    );
     
            //方法一
            return Content(excelToHtmlConverter.Document.InnerXml);
            
            //方法二
            ////输出的html文件   需创建对应的文件目录  这里是根目录下的doc文件夹
            //var htmlFile = System.Web.HttpContext.Current.Server.MapPath("/") + "doc\\" + fileName + ".html";
            //excelToHtmlConverter.Document.Save(htmlFile);

            //Response.Redirect("http://" + System.Web.HttpContext.Current.Request.Url.Host + ":" +
            //    System.Web.HttpContext.Current.Request.Url.Port + "/doc/" + fileName + ".html");
        }

        private string Content(StringBuilder sbRet)
        {
          
           return sbRet.ToString();
            
        }

        private string Content(string innerXml)
        {
            return innerXml;
        }

        public string ToHtml(int sheetIndex = 0, bool completeHtmls = true)
        {
            return ToHtml(wb.GetSheetName(sheetIndex), completeHtmls);
        }

        public string ToHtml(string sheetName, bool completeHtmls = true)
        {
            StringBuilder sbRet = new StringBuilder();

            if (completeHtmls)
            {
                sbRet.Append("<?xml version=\"1.0\" encoding=\"iso-8859-1\" ?>\n");
                sbRet.Append("<html>\n");
                sbRet.Append("<head>\n");
            }
            sbRet.Append(GetInlineStyle());
            if (completeHtmls)
            {
                sbRet.Append("</head>\n");
                sbRet.Append("<body>\n");
            }
            sbRet.Append(GetSheets(sheetName));
            if (completeHtmls)
            {
                sbRet.Append("</body>\n");
                sbRet.Append("</html>\n");
            }

            return sbRet.ToString();
        }

        private List<KeyValuePair<HorizontalAlignment, string>> HALIGN = new List<KeyValuePair<HorizontalAlignment, string>>() {
            new KeyValuePair<HorizontalAlignment, string>(HorizontalAlignment.Left, "left"),
            new KeyValuePair<HorizontalAlignment, string>(HorizontalAlignment.Center, "center"),
            new KeyValuePair<HorizontalAlignment, string>(HorizontalAlignment.Right, "right"),
            new KeyValuePair<HorizontalAlignment, string>(HorizontalAlignment.Fill, "left"),
            new KeyValuePair<HorizontalAlignment, string>(HorizontalAlignment.Justify, "left"),
            new KeyValuePair<HorizontalAlignment, string>(HorizontalAlignment.CenterSelection, "center"),
            new KeyValuePair<HorizontalAlignment, string>(HorizontalAlignment.General, "left")
        };

        private List<KeyValuePair<VerticalAlignment, string>> VALIGN = new List<KeyValuePair<VerticalAlignment, string>>() {
            new KeyValuePair<VerticalAlignment, string>(VerticalAlignment.Bottom, "bottom"),
            new KeyValuePair<VerticalAlignment, string>(VerticalAlignment.Center, "middle"),
            new KeyValuePair<VerticalAlignment, string>(VerticalAlignment.Top, "top")
        };

        private List<KeyValuePair<BorderStyle, string>> BORDER = new List<KeyValuePair<BorderStyle, string>>() {
            new KeyValuePair<BorderStyle, string>(BorderStyle.DashDot, "dashed 1pt"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.DashDotDot, "dashed 1pt"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.Dashed, "dashed 1pt"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.Dotted, "dotted 1pt"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.Double, "double 3pt"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.Hair, "dashed 1px"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.Medium, "solid 2pt"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.MediumDashDot, "dashed 2pt"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.MediumDashDotDot, "dashed 2pt"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.MediumDashed, "dashed 2pt"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.None, "none"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.SlantedDashDot, "dashed 2pt"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.Thick, "solid 3pt"),
            new KeyValuePair<BorderStyle, string>(BorderStyle.Thin, "solid 1pt")
        };

       
       

        private string GetSheets(string sheetName)
        {
            StringBuilder sbRet = new StringBuilder();

            ISheet sheet = wb.GetSheet(sheetName);
            sbRet.Append(GetSheet(sheet));

            return sbRet.ToString();
        }

        private string GetSheet(ISheet sheet)
        {
            StringBuilder sbRet = new StringBuilder();
            ensureColumnBounds(sheet);
            sbRet.Append(string.Format("<table class={0} cellspacing=\"0\" cellpadding=\"0\" >\n", DEFAULTS_CLASS));
            sbRet.Append(GetCols(sheet));
            sbRet.Append(GetSheetContent(sheet));
            sbRet.Append("</table>\n");

            return sbRet.ToString();
        }


        private string GetSheetContent(ISheet sheet)
        {
            CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.BuildMergedRangesMap(sheet);
            StringBuilder sbRet = new StringBuilder();
            sbRet.Append(string.Format("<tbody>\n"));
            int physicalNumberOfRows = sheet.PhysicalNumberOfRows;
            if (physicalNumberOfRows <= 0)
            {
                return "";
            }

            IEnumerator rows = sheet.GetRowEnumerator();
            for (int r = 0; r < physicalNumberOfRows; r++)
            {
                IRow row = sheet.GetRow(r);
                if (row == null)
                    continue;
               

                sbRet.Append(string.Format("  <tr>\n"));

                ICell cell = null;
                firstColumn = 0;
                endColumn = row.LastCellNum;
                for (int i = firstColumn; i < endColumn; i++)
                {

                    CellRangeAddress range = ExcelToHtmlUtils.GetMergedRange(
                        mergedRanges, row.RowNum, i);

                    if (range != null && (range.FirstColumn != i || range.FirstRow != row.RowNum))
                        continue;

                    String content = " ";
                    String attrs = "";
                    ICellStyle style = null;

                    try
                    {

                    }
                    catch (Exception)
                    {

                        throw;
                    }
                    if (i >= row.FirstCellNum && i < row.LastCellNum)
                    {
                        cell = row.GetCell(i);
                        if (cell != null)
                        {
                            style = cell.CellStyle;
                            attrs = tagStyle(cell, style);
                            //Set the value that is rendered for the cell
                            //also applies the format
                            MyCellFormat cf = MyCellFormat.GetInstance(style.GetDataFormatString());
                            var q = cf.GetType();


                            CellFormatResult result = cf.Apply(cell);
                            content = result.Text; //never null
                            if (string.IsNullOrEmpty(content))
                            {
                                content = " ";
                            }

                        }
                    }
                    string colspan = "";
                    string rowspan = "";
                    if (range != null)
                    {
                        if (range.FirstColumn != range.LastColumn)
                        {
                            colspan = " colspan=" + (range.LastColumn - range.FirstColumn + 1).ToString();
                        }

                        if (range.FirstRow != range.LastRow)
                        {
                            rowspan = " rowspan=" + (range.LastRow - range.FirstRow + 1).ToString();
                        }
                    }
                    sbRet.Append(string.Format("    <td class=\"{0}\" {1}{3}{4}>{2}</td>\n", styleName(style), attrs, content, colspan, rowspan));
                }
                sbRet.Append(string.Format("  </tr>\n"));
            }
            sbRet.Append(string.Format("</tbody>\n"));


            return sbRet.ToString();
        }

        private String tagStyle(ICell cell, ICellStyle style)
        {
            if (style.Alignment == HorizontalAlignment.General)
            {
                switch (ultimateCellType(cell))
                {
                    case CellType.String:
                        return "style=\"text-align: left;\"";
                    case CellType.Boolean:
                    case CellType.Error:
                        return "style=\"text-align: center;\"";
                    case CellType.Numeric:
                    default:
                        // "right" is the default
                        break;
                }
            }
            return "";
        }

        private static CellType ultimateCellType(ICell c)
        {
            CellType type = c.CellType;
            if (type == CellType.Formula)
            {
                type = c.CachedFormulaResultType;
            }
            return type;
        }
        protected static int GetColumnWidth(ISheet sheet, int columnIndex)
        {
            return ExcelToHtmlUtils.GetColumnWidthInPx(sheet.GetColumnWidth(columnIndex));
        }

        private string GetCols(ISheet sheet)
        {
            StringBuilder sbRet = new StringBuilder();
            for (int i = firstColumn; i < endColumn; i++)
            {
                int colWidth = GetColumnWidth( sheet,i);
                sbRet.Append(string.Format("<col style=\"width:{0}px;\"/>\n", colWidth));
            }
            return sbRet.ToString();
        }

        private void ensureColumnBounds(ISheet sheet)
        {
            if (gotBounds) return;

            IEnumerator iter = sheet.GetRowEnumerator();
            if (iter.MoveNext()) firstColumn = 0;
            else firstColumn = int.MaxValue;

            endColumn = 0;
            iter.Reset();
            while (iter.MoveNext())
            {
                IRow row = (IRow)iter.Current;
                short firstCell = row.FirstCellNum;
                if (firstCell >= 0)
                {
                    firstColumn = Math.Min(firstColumn, firstCell);
                    endColumn = Math.Max(endColumn, row.LastCellNum);
                }
            }
            gotBounds = true;
        }

        private string GetInlineStyle()
        {
            StringBuilder sbRet = new StringBuilder();

            sbRet.Append("<style type=\"text/css\">\n");
            sbRet.Append(GetStyles());
            sbRet.Append("</style>\n");

            return sbRet.ToString();
        }

        public string GetStyles()
        {
            StringBuilder sbRet = new StringBuilder();

            HashSet<ICellStyle> seen = new HashSet<ICellStyle>();
            for (int i = 0; i < wb.NumberOfSheets; i++)
            {
                ISheet sheet = wb.GetSheetAt(i);
                IEnumerator rows = sheet.GetRowEnumerator();
                while (rows.MoveNext())
                {
                    IRow row = (IRow)rows.Current;
                    foreach (ICell cell in row)
                    {
                        ICellStyle style = cell.CellStyle;
                        if (!seen.Contains(style))
                        {
                            sbRet.Append(GetStyle(style));
                            seen.Add(style);
                        }
                    }
                }
            }

            return sbRet.ToString();
        }

        public string GetStyle(ICellStyle style)
        {
            StringBuilder sbRet = new StringBuilder();

            sbRet.Append(string.Format(".{0} .{1} {{\n", DEFAULTS_CLASS, styleName(style)));
            sbRet.Append(styleContents(style));
            sbRet.Append("}\n");

            return sbRet.ToString();
        }

        private string styleContents(ICellStyle style)
        {
            StringBuilder sbRet = new StringBuilder();

            sbRet.Append(styleOut("text-align", style.Alignment));
            sbRet.Append(styleOut("vertical-align", style.VerticalAlignment));
            sbRet.Append(fontStyle(style));
            sbRet.Append(borderStyles(style));
            sbRet.Append(colorStyles(style));

            return sbRet.ToString();
        }

        private string colorStyles(ICellStyle style)
        {
            StringBuilder sbRet = new StringBuilder();

            //sbRet.Append("还未实现！");

            return sbRet.ToString();
        }

        private string borderStyles(ICellStyle style)
        {
            StringBuilder sbRet = new StringBuilder();

            sbRet.Append(styleOut("border-left", style.BorderLeft));
            /*
             * NPOI有BUG，合并单元格的 border-right 永远都是 None
             * 我们可以通过设置合并单元格后边那个单元格的左边框的解决
             * 如果当前合并单元格已经合并到最后一列了，我们就只能再加一列了，为了不影响打印效果
             * 这最后加的这一列在设置好左边框后，需要把宽度设置得很小，比如说0.1这样
             */
            sbRet.Append(styleOut("border-right", style.BorderRight));
            sbRet.Append(styleOut("border-top", style.BorderTop));
            sbRet.Append(styleOut("border-bottom", style.BorderBottom));

            return sbRet.ToString();
        }

        private string fontStyle(ICellStyle style)
        {
            StringBuilder sbRet = new StringBuilder();

            IFont font = style.GetFont(wb);

            if (font.IsBold)
            {
                sbRet.Append("  font-weight: bold;\n");
            }
            if (font.IsItalic)
            {
                sbRet.Append("  font-style: italic;\n");
            }

            double fontheight = font.FontHeight / 10 - 10;
            if (fontheight == 9)
            {
                //fix for stupid ol Windows
                fontheight = 10;
            }
            sbRet.Append(string.Format("  font-size: {0}pt;\n", fontheight));

            return sbRet.ToString();
        }

        private string styleOut(string k, HorizontalAlignment p)
        {
            return k + ":" + HALIGN.Where(o => o.Key == p).First().Value + ";\n";
        }
        private string styleOut(string k, VerticalAlignment p)
        {
            return k + ":" + VALIGN.Where(o => o.Key == p).First().Value + ";\n";
        }
        private string styleOut(string k, BorderStyle p)
        {
            return k + ":" + BORDER.Where(o => o.Key == p).First().Value + ";\n";
        }

        private string styleName(ICellStyle style)
        {
            if (style == null)
            {
                style = wb.GetCellStyleAt((short)0);
            }
            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("style_{0}", style.Index));
            return sb.ToString();
        }

    }

    /// <summary>
    /// 这个东西是为了解决 NPOI CellFormat 的BUG而存在的。
    /// 它在读取 日期格式 的时候有时候会报错。
    /// </summary>
    public class MyCellFormat
    {
        private CellFormat cellformat = null;

        private MyCellFormat(string format)
        {
            this.cellformat = CellFormat.GetInstance(format);
        }

        public static MyCellFormat GetInstance(string format)
        {
            return new MyCellFormat(format);
        }

        public CellFormatResult Apply(ICell cell)
        {
            try
            {
                if (cell.CellType == CellType.Numeric)
                {
                    DateTime.FromOADate(double.Parse(cell.NumericCellValue.ToString()));
                 
               
                }
           



                return cellformat.Apply(cell);
            }
            catch (Exception)
            {
                var formatStr = cell.CellStyle.GetDataFormatString();
                var mc = new Regex(@"(yy|M|d|H|s|ms)").Match(formatStr);
                /*
                 * 目前全部不能正常转换的日期格式都转换成 yyyy - MM - dd 的形式
                 * 比如说：【[$-F800]dddd\,\ mmmm\ dd\,\ yyyy】这个格式
                 * 稍微 google 了下（ https://msdn.microsoft.com/en-us/library/dd318693(VS.85).aspx）
                 * 这个字符串 0x0800 表示 [System default locale language]
                 * 因时间关系，只能干完手头的活之后再慢慢研究了。
                 */
                if (mc.Success)
                {
                    return CellFormat.GetInstance("yyyy-MM-dd").Apply(cell);
                }
                else return cellformat.Apply(cell.ToString() + "<!-- This is the bug of NPOI, Maybe you should modify the file which name is \"MyCellFormat.cs\" -->");
            }
        }

        public CellFormatResult Apply2(XSSFCell cell)
        {
            try
            {

                GetValueTypeForXLSX(cell);


                return cellformat.Apply(cell);
            }
            catch (Exception)
            {
                var formatStr = cell.CellStyle.GetDataFormatString();
                var mc = new Regex(@"(yy|M|d|H|s|ms)").Match(formatStr);
                /*
                 * 目前全部不能正常转换的日期格式都转换成 yyyy - MM - dd 的形式
                 * 比如说：【[$-F800]dddd\,\ mmmm\ dd\,\ yyyy】这个格式
                 * 稍微 google 了下（ https://msdn.microsoft.com/en-us/library/dd318693(VS.85).aspx）
                 * 这个字符串 0x0800 表示 [System default locale language]
                 * 因时间关系，只能干完手头的活之后再慢慢研究了。
                 */
                if (mc.Success)
                {
                    return CellFormat.GetInstance("yyyy-MM-dd").Apply(cell);
                }
                else return cellformat.Apply(cell.ToString() + "<!-- This is the bug of NPOI, Maybe you should modify the file which name is \"MyCellFormat.cs\" -->");
            }
        }


        /// <summary>
        /// 获取单元格类型(xlsx)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLSX(XSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }

        public CellFormatResult Apply(Object v)
        {
            return cellformat.Apply(v);
        }
    }
}
